package com.cnblsp2.android.study.dbandio.db;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

import com.cnblsp2.android.study.dbandio.R;

/**
 * 需要注意的是：SimpleCursorAdapter只能识别列名为"_id"的主键，因此程序创建数据表时指定了主键列的列名为"_id"
 * @author cpic
 *
 */
public class DBActivity extends Activity {

	private SQLiteDatabase db = null;
	private Button btn = null;
	private ListView listView;

	private EditText titleEt = null;
	private EditText contentEt = null;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_db);

		// 创建或打开数据库(此处需要使用绝对路径)
		db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString() + "/dbtest.db3", null);
		listView = (ListView) findViewById(R.id.activity_db_show_lv);
		btn = (Button) findViewById(R.id.activity_db_ok_btn);
		titleEt = (EditText) findViewById(R.id.activity_db_title_et);
		contentEt = (EditText) findViewById(R.id.activity_db_content_et);

		btn.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				// 获取用户输入
				String title = titleEt.getText().toString();
				String content = contentEt.getText().toString();
				try {
					insertData(db, title, content);
					Cursor cursor = db.rawQuery("select * from news_inf", null);
					inflateList(cursor);
				} catch (Exception e) {
					// 执行DLL创建数据表
					db.execSQL("create table news_inf(_id integer primary key autoincrement,news_title varchar(50),news_content varchar(255) )");
					// db.execSQL("drop table news_inf");
					// 执行insert语句插入数据
					insertData(db, title, content);
					// 执行查询
					Cursor cursor = db.rawQuery("select * from news_inf", null);
					inflateList(cursor);
				}
			}
		});
		Cursor cursor = db.rawQuery("select * from news_inf", null);
		inflateList(cursor);
	}

	private void insertData(SQLiteDatabase db, String title, String content) {
		db.execSQL("insert into news_inf (_id,news_title,news_content) values(null,?,?)", new String[] { title, content });
	}

	private void inflateList(Cursor cursor) {
		// 填充SimpleCursorAdapter
		SimpleCursorAdapter adapter = new SimpleCursorAdapter(DBActivity.this, R.layout.activity_db_line, cursor, new String[] { "news_title",
				"news_content" }, new int[] { R.id.activity_db_title, R.id.activity_db_content });
		listView.setAdapter(adapter);
	}

	@Override
	protected void onDestroy() {
		// TODO Auto-generated method stub
		super.onDestroy();
		// 程序程序时关闭SQLiteDatabase
		if (db != null && db.isOpen()) {
			db.close();
		}

	}

}
